"""
Case Type   : 防篡改
Case Name   : 三权分立关闭时验证超级用户对其他用户创建的模式有USAGE权限,而且该权限不会被收回
Create At   : 2022-02-24
Owner       : lwx1080719
Description :
    1.查询三权分立参数enableSeparationOfDuty
    2.创建不同权限用户
    3.createrole用户登录数据库创建模式,普通表,分区
    4.sysadmin用户登录数据库对普通表进行insert,select,update,delete,truncate操作
    5.sysadmin用户登录数据库对分区表进行insert,select,update,delete,truncate操作
    6.清理环境
Expect      :
    1.显示默认值off
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
History     : 
"""



import os
import unittest
from yat.test import macro
from yat.test import Node
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common
from testcase.utils.Logger import Logger


class ModifyCase(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.dbuserNode = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Common = Common()
        self.Constant = Constant()
        self.user_name1 = 'u_security_usage_privilege_0001_01'
        self.user_name2 = 'u_security_usage_privilege_0001_02'
        self.table_name = 't_security_usage_privilege_0001'
        self.schema_name = 's_security_usage_privilege_0001'
        self.default_value = self.Common.show_param('enableSeparationOfDuty')

    def test_security(self):
        text = '----step1:查询三权分立参数enableSeparationOfDuty值  ' \
               'expect:默认值off----'
        self.logger.info(text)
        self.assertEqual("off", self.default_value, "执行失败:" + text)

        text = '----step2:创建不同权限用户及数据库 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            create user {self.user_name1} createrole password 
            '{macro.PASSWD_REPLACE}';
            create user {self.user_name2} sysadmin password 
            '{macro.PASSWD_REPLACE}';
            GRANT create,connect on database {self.dbuserNode.db_name}
             TO {self.user_name1} WITH GRANT OPTION;''')
        self.logger.info(sql_cmd)
        self.assertTrue(
            sql_cmd.count(self.Constant.CREATE_ROLE_SUCCESS_MSG) == 2,
            "执行失败:" + text)

        text = '----step3:createrole用户登录数据库创建模式,普通表,分区表 ' \
               'expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
            create schema {self.schema_name};
            create table {self.schema_name}.{self.table_name}_1
            (id int,name varchar(100));
            create table {self.schema_name}.{self.table_name}_2
            (id int,name varchar(100)) partition by range(id) 
            (partition p1 values less than(10),
            partition p2 values less than(20),
            partition p3 values less than(30),
            partition pmax values less than(maxvalue));'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name1} '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertTrue(msg.count(self.Constant.CREATE_TABLE_SUCCESS) == 2,
                        "执行失败:" + text)
        self.assertIn(self.Constant.CREATE_SCHEMA_SUCCESS_MSG, msg,
                      "执行失败:" + text)

        text = '----step5:sysadmin用户登录数据库对普通表' \
               '进行insert,select,update,delete,truncate操作 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
            insert into {self.schema_name}.{self.table_name}_1 
            values(1,'beijing'),(2,'shanghai');
            select * from {self.schema_name}.{self.table_name}_1;
            delete from {self.schema_name}.{self.table_name}_1 where id=2;
            update {self.schema_name}.{self.table_name}_1 
            set name='shanxi' where id=1;
            truncate table {self.schema_name}.{self.table_name}_1;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name2} '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, msg,
                      '执行失败:' + text)
        self.assertIn(self.Constant.DELETE_SUCCESS_MSG, msg,
                      '执行失败:' + text)
        self.assertIn(self.Constant.UPDATE_SUCCESS_MSG, msg,
                      '执行失败:' + text)
        self.assertIn(self.Constant.TRUNCATE_SUCCESS_MSG, msg,
                      '执行失败:' + text)
        self.assertIn('2 rows', msg, '执行失败:' + text)

        text = '----step6:sysadmin用户登录数据库对分区表' \
               '进行insert,select,update,delete,truncate操作 expect:成功----'
        self.logger.info(text)
        sql_cmd = f'''
            insert into {self.schema_name}.{self.table_name}_2 
            values(1,'beijing'),(2,'shanghai');
            select * from {self.schema_name}.{self.table_name}_2;
            delete from {self.schema_name}.{self.table_name}_2 where id=2;
            update {self.schema_name}.{self.table_name}_1 
            set name='shanxi' where id=1;
            truncate table {self.schema_name}.{self.table_name}_2;'''
        self.logger.info(sql_cmd)
        msg = self.primary_sh.execut_db_sql(
            sql_cmd, sql_type=f' -U {self.user_name2} '
            f'-W {macro.PASSWD_REPLACE}')
        self.logger.info(msg)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG and
                      self.Constant.DELETE_SUCCESS_MSG and
                      self.Constant.UPDATE_SUCCESS_MSG and
                      self.Constant.TRUNCATE_SUCCESS_MSG and
                      '2 rows', msg, '执行失败:' + text)

    def tearDown(self):
        text = '----step7:清理环境 expect:成功----'
        self.logger.info(text)
        sql_cmd = self.primary_sh.execut_db_sql(f'''
            drop user {self.user_name1} cascade;
            drop user {self.user_name2} cascade;''')
        self.logger.info(sql_cmd)
        self.assertTrue(
            sql_cmd.count(self.Constant.DROP_ROLE_SUCCESS_MSG) == 2,
            "执行失败:" + text)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
